using System;
using System.Data;
using System.Collections.Generic;
namespace eTMS.DAO{
	/********************************BEGIN SinhVienLopTinChiDAO****************************************/
	///<summary>
	/// <para>Author: Auto generated class by CSharp Code Generator v2.0</para>
	/// <para>Date:04/09/2010 42:05:29</para>
	/// <para>Class Name: SinhVienLopTinChiDAO</para>
	/// <para>Description: Class access to table SinhVienLopTinChi in database. Class for coder code here</para>
	///</summary>

	
	public partial class SinhVienLopTinChiDAO{
        public DataSet SelectAllDSWithLopTinChiBySinhVien(int SinhVien_ID, int HocKy_ID, int NamHoc_ID)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT  LopTinChi.MonHoc_ID,LopTinChi.LopTinChi_ID,LopTinChi.MaLop, LopTinChi.SoSVToiDa, LopTinChi.TrangThai,SinhVienLopTinChi.SinhVienLopTinChi_ID, SinhVienLopTinChi.SinhVien_ID, SinhVienLopTinChi.TrangThai AS TrangThaiDangKy
                                FROM    SinhVienLopTinChi INNER JOIN
                                        LopTinChi ON SinhVienLopTinChi.LopTinChi_ID = LopTinChi.LopTinChi_ID 
                                WHERE   LopTinChi.HocKy_ID=" +HocKy_ID.ToString()+" AND LopTinChi.NamHoc_ID="+NamHoc_ID.ToString()+" AND SinhVienLopTinChi.SinhVien_ID="+SinhVien_ID.ToString();
            return this.SelectDS(cmd);
        }
        public DataSet SelectAllCountSinhVienDKByListLopTinChi( List<int> LsLopTinChi_ID)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT 
                                SinhVienLopTinChi.LopTinChi_ID,
                                SUM(CASE WHEN SinhVienLopTinChi.TrangThai=1 THEN 1 ELSE 0 END) as SoSVMDK,
                                SUM(CASE WHEN SinhVienLopTinChi.TrangThai=2 THEN 1 ELSE 0 END) as SoSVDDK,
                                SUM(CASE WHEN SinhVienLopTinChi.TrangThai=2 THEN 1 ELSE 0 END) as SoSVHDK
                                FROM SinhVienLopTinChi
                                WHERE   SinhVienLopTinChi.LopTinChi_ID IN(" + string.Join(",",LsLopTinChi_ID.ConvertAll<string>(Convert.ToString).ToArray())+@")
                                GROUP BY SinhVienLopTinChi.LopTinChi_ID";
            return this.SelectDS(cmd);
        }
        public DataSet SelectAllCountSinhVienDKWithLopTinChiByListMonHoc(int NamHoc_ID,int HocKy_ID,List<int> LsMonHoc_ID)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT 
                                LopTinChi.LopTinChi_ID,LopTinChi.MaLop,LopTinChi.NamHoc_ID,LopTinChi.HocKy_ID,LopTinChi.SoSVToiDa,LopTinChi.MonHoc_ID,LopTinChi.TrangThai,
                                MonHoc.MaMonHoc,MonHoc.TenMonHoc,
                                SUM(CASE WHEN SinhVienLopTinChi.TrangThai=1 THEN 1 ELSE 0 END) as SoSVMDK,
                                SUM(CASE WHEN SinhVienLopTinChi.TrangThai=2 THEN 1 ELSE 0 END) as SoSVDDK,
                                SUM(CASE WHEN SinhVienLopTinChi.TrangThai=2 THEN 1 ELSE 0 END) as SoSVHDK
                                FROM LopTinChi LEFT JOIN SinhVienLopTinChi ON SinhVienLopTinChi.LopTinChi_ID=LopTinChi.LopTinChi_ID 
                                     INNER JOIN MonHoc ON  LopTinChi.MonHoc_ID=MonHoc.MonHoc_ID
                                WHERE   LopTinChi.NamHoc_ID=" + NamHoc_ID.ToString() + " AND LopTinChi.HocKy_ID="+HocKy_ID.ToString()+(LsMonHoc_ID.Count>0? " AND LopTinChi.MonHoc_ID IN(" + string.Join(",", LsMonHoc_ID.ConvertAll<string>(Convert.ToString).ToArray()) + @")":"")+@"
                                GROUP BY LopTinChi.LopTinChi_ID,LopTinChi.MaLop,LopTinChi.NamHoc_ID,LopTinChi.HocKy_ID,LopTinChi.SoSVToiDa,LopTinChi.MonHoc_ID,LopTinChi.TrangThai,
                                MonHoc.MaMonHoc,MonHoc.TenMonHoc";
            return this.SelectDS(cmd);
        }
	}
	/*****************************************************************************************************/
	/**********************************END SinhVienLopTinChiDAO*****************************************/
	/*****************************************************************************************************/
}

